from .Query import Query


class QueryFailByMajor(Query):
    """
    根据专业、年级、学年、学期，查询统计该专业所有课程不及格学生的成绩明细，按照学年、学期、课程名称、课程代码进行分组显示
    """
    __sql = """
    SELECT
        course.STUDENTNO,
        course.STUNAME,
        students.MAJOR,
        course.GRADE,
        course.ACADEMICYEAR,
        course.TERM,
        COURSECODE,
        COURSENAME,
        FINALSCORE
    FROM
        t_courseinfo_students course
    JOIN t_stuscoreconf_master students ON course.STUDENTNO = students.STUDENTNO
    WHERE
        (
            FINALSCORE IN ('不及格', '不通过')
            OR FINALSCORE IS NULL
            OR FINALSCORE < '60'
        )
    AND course.MAJOR = %s
    AND course.GRADE = %s
    AND course.ACADEMICYEAR LIKE %s
    AND course.TERM LIKE %s
    """

    def __init__(self, request):
        major = Query.len_zero_to_default(request.get("major", "%"))
        grade = Query.len_zero_to_default(request.get("grade", "%"))
        academic_year = Query.len_zero_to_default(
            request.get("academic_year", "%"))
        term = Query.len_zero_to_default(request.get("term", "%"))
        self.execute_sql(self.__sql,
                         major,
                         grade,
                         academic_year,
                         term
                         )
